package com.arsl.server.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DBConnector {

	static Connection con = null;
	Statement st = null;
	ResultSet rs = null;

	//static String url = "jdbc:mysql://ec2-54-251-104-51.ap-southeast-1.compute.amazonaws.com:3306/paperazi?useUnicode=yes&characterEncoding=UTF-8";
	static String url = "jdbc:mysql://localhost:3306/paperazi?useUnicode=yes&characterEncoding=UTF-8";
	static String user = "root";
	static String password = "";

	static Logger logger = Logger.getLogger("DBConnector");

	/**
	 * 
	 * @param sp
	 * @param params
	 * @param types
	 *            For simple queries without returns
	 */
	public static void runSP(String sp, Object[] params, int[] types) {
		runSP(sp, params, types, null, false, null, null);
	}

	/**
	 * 
	 * @param sp
	 * @param params
	 * @param types
	 * @param returnTypes
	 *            For Sps with return values
	 */
	public static Object[] runSP(String sp, Object[] params, int[] types,
			int[] returnTypes) {
		return runSP(sp, params, types, returnTypes, false, null, null);
	}

	/**
	 * 
	 * @param sp
	 * @param params
	 * @param types
	 * @param expectResultSet
	 * @param clazz
	 *            For Sps with return resultsets
	 */
	public static Object[] runSP(String sp, Object[] params, int[] types,
			boolean expectResultSet, ResultSetMapper mapper, Class clazz) {
		return runSP(sp, params, types, null, expectResultSet, mapper, clazz);
	}

	/**
	 * The real function
	 * 
	 * @param sp
	 * @param params
	 * @param types
	 * @param returnTypes
	 * @param expectResultSet
	 * @param clazz
	 * @param clazz
	 * @return
	 */
	public static Object[] runSP(String sp, Object[] params, int[] types,
			int[] returnTypes, boolean expectResultSet, ResultSetMapper mapper,
			Class clazz) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, user, password);

			StringBuilder callStmt = new StringBuilder("{call " + sp + "(");
			StringBuilder argStr = new StringBuilder("");
			for (int i = 0; i < params.length; i++) {
				argStr.append("?,");
			}
			if (returnTypes != null) {
				for (int i = 0; i < returnTypes.length; i++) {
					argStr.append("?,");
				}
			}
			if (argStr.length() > 0)
				callStmt.append(argStr.substring(0, argStr.length() - 1));
			callStmt.append(")}");
			System.out.println(callStmt.toString());
			CallableStatement cs = con.prepareCall(callStmt.toString());

			for (int i = 0; i < types.length; i++) {
				cs.setObject(i + 1, params[i], types[i]);
			}

			if (returnTypes != null) {
				for (int i = 0; i < returnTypes.length; i++) {
					cs.registerOutParameter(types.length + i + 1,
							returnTypes[i]);
				}
			}

			ResultSet rs = cs.executeQuery();
			if (expectResultSet) {
				List list = null;
				try {

					list = mapper.mapRersultSetToObject(rs, clazz);

					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				return list.toArray();

			}

			if (returnTypes != null) {
				Object[] returns = new Object[returnTypes.length];
				for (int i = 0; i < returnTypes.length; i++) {
					if (returnTypes[i] == Types.INTEGER) {
						returns[i] = cs.getInt(types.length + i + 1);
					} else if (returnTypes[i] == Types.VARCHAR) {
						returns[i] = cs.getString(types.length + i + 1);
					} else if (returnTypes[i] == Types.FLOAT) {
						returns[i] = cs.getFloat(types.length + i + 1);
					}
				}

				con.close();
				return returns;
			}
			con.close();
			return null;
		} catch (SQLException e) {

			logger.log(Level.SEVERE, e.getMessage());
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {

			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
